<?php

	class Model_Paper extends Zend_Db_Table_Abstract
	{
   	protected $_table;

    public function getTable()
    {
        if (null === $this->_table) {            
            require_once APPLICATION_PATH . '/models/DbTable/Paper.php';
            $this->_table = new Model_DbTable_Paper;
        }
        return $this->_table;
    }
   
    public function save(array $data)
    {
    	if(isset($data['id'])){
    		$id = $data['id'];
    	}else{
    		$id = 0;
    	}
    		
        $table  = $this->getTable();
        $fields = $table->info(Zend_Db_Table_Abstract::COLS);        
        foreach ($data as $field => $value) {
            if (!in_array($field, $fields)) {
                unset($data[$field]);                
            }
            
        }
        
        if($id == 0){
        	return $table->insert($data);
        }else{
        	$where = $table->getAdapter()->quoteInto('id = ?', $id);
        	return $table->update($data, $where);
        }
    }
   
    public function fetchEntries()
    {       
        return $this->getTable()->fetchAll('1');
    }
    
	 public function getPapersById($id){
	  	$table = $this->getTable();		 			
			$db = $table->getAdapter();			
			$select = $db->select()->from(array('p' => 'papers'), array('id', 'title', 'date_sent', 'file_doc', 'file_pdf'))
									 ->join(array('ps' => 'paper_status'), 'p.status_id = ps.id',
              			array('status' => 'name'));
			$stmt = $select->query();			
      return $stmt->fetchAll();
    }
	
	public function getByParticipantId($id){
	  	$table = $this->getTable();		 			
		$db = $table->getAdapter();			
		$sql = 'SELECT p.id, p.title, p.date_sent, p.file_pdf, p.file_doc, p.status_id, ps.name_mk as paper_status_mk, ps.name_en as paper_status_en, p.abstract,
				(SELECT COUNT(r.id) FROM reviews r JOIN assignments a ON r.assignment_id = a.id WHERE a.paper_id = p.id) as reviews_count, ar.name_en as area_name_en, ar.name_mk as area_name_mk, c.name_en as category_name_en, c.name_mk as category_name_mk
				FROM papers p
				JOIN categories c ON c.id = p.category_id
				JOIN areas ar ON ar.id = p.area_id
				JOIN paper_status ps ON ps.id = p.status_id
				WHERE p.isActive = true AND p.participant_id = ' . $id . '
				ORDER BY p.date_sent';

		$res = $db->query($sql);    			
      	return $res->fetchAll();
    }
    public function checkForDownload($paper_id, $user_id){
		$table = $this->getTable();
		$db = $table->getAdapter();	
		$sql = 'SELECT COUNT(id) FROM papers WHERE isActive = 1 AND participant_id = ' . $user_id . ' AND id = ' . $paper_id;
		$res1 = $db->query($sql);    	
        $res1->fetchAll();
		$sql = 'SELECT id FROM assignments WHERE isActive = 1 AND reviewer_id = ' . $user_id . ' AND paper_id = ' . $paper_id;
		$res2 = $db->query($sql);    	
		if($res1->fetchColumn(0) == 1){
			return 0;
		}
		return $res2->fetchColumn(0);
		return -1;
	}
    public function getPapersForReviewerId($id){
	  	$table = $this->getTable();				
		$db = $table->getAdapter();			
		$sql = 'SELECT p.id, p.title, p.date_sent, p.file_pdf, p.file_doc, p.status_id, ps.name_en as paper_status, p.abstract,
				(SELECT COUNT(r.id) FROM reviews r JOIN assignments a ON r.assignment_id = a.id WHERE a.paper_id = p.id AND a.reviewer_id = ' . $id . ') as reviews_count, a.id as assignment_id, ar.name_en as area_name_en, ar.name_mk as area_name_mk, c.name_en as category_name_en, c.name_mk as category_name_mk
				FROM papers p
				JOIN paper_status ps ON ps.id = p.status_id
				JOIN assignments a ON a.paper_id = p.id
				JOIN categories c ON c.id = p.category_id
				JOIN areas ar ON ar.id = p.area_id
				WHERE p.isActive = 1 AND a.isActive = 1 AND a.reviewer_id = ' . $id . '
				ORDER BY a.date_assigned';

		$res = $db->query($sql);    			
      	return $res->fetchAll();		
    }
	public function getUnassigned($area_id, $sort, $dir){//, $limit, $start){
		$table = $this->getTable();		 			
		$db = $table->getAdapter();			
		$select = $db->select()->from(array('p' => 'papers'))
							 ->join(array('c' => 'categories'), 'p.category_id = c.id',
              			array('category_name' => 'name_en'))							
						->where('p.isAssigned = 0')
						->where('p.area_id = ?', $area_id)
              			->where('p.isActive = 1')
              			->where('p.status_id < 5')
						->order($sort . ' ' . $dir);
						//->limit($limit, $start);
		$stmt = $select->query();			
      	return $stmt->fetchAll();
	}
	public function getUnassignedCount($area_id){
    	$table = $this->getTable();		 			
		$db = $table->getAdapter();	
		$sql = 'SELECT COUNT(id) 
				FROM papers 
				WHERE isAssigned = 0 AND isActive = 1 AND status_id < 5 AND area_id = ' . $area_id;
		$res = $db->query($sql);    	
    	return $res->fetchColumn(0);
    }
	
	public function getAssigned($area_id, $sort, $dir){//, $limit, $start){
		$table = $this->getTable();		 			
		$db = $table->getAdapter();			
		$select = $db->select()->from(array('p' => 'papers'))
							 ->join(array('c' => 'categories'), 'p.category_id = c.id',
              			array('category_name' => 'name_en'))							
							->join(array('pa' => 'assignments'), 'pa.paper_id = p.id', array())
              			->where('pa.isActive = 1')
						->where('p.area_id = ?', $area_id)
						->group('pa.paper_id')
						->order($sort . ' ' . $dir);
						//->limit($limit, $start);
		$stmt = $select->query();			
      	return $stmt->fetchAll();
	}
	public function getAssignedCount($area_id){
    	$table = $this->getTable();		 			
		$db = $table->getAdapter();	
		$sql = 'SELECT COUNT(id) 
				FROM papers 
				WHERE isAssigned = 1 AND status_id < 5 AND area_id = ' . $area_id;
		$res = $db->query($sql);    	
    	return $res->fetchColumn(0);
    }
	public function setAssigned($assigned, $assign){
		foreach($assigned as $key => $val){
			$p = $this->fetchEntry($key);
			$p->isAssigned = $assign;
			$p->save();
		}
	}
    public function getPapersByAreaId($area_id){
    	$table = $this->getTable();		 			
		$db = $table->getAdapter();			
		$select = $db->select()->from(array('p' => 'papers'), array('id', 'title', 'date_sent'))
							 ->join(array('c' => 'categories'), 'p.category_id = c.id',
              			array('category_name' => 'name_en'))
              			->where('p.isActive = 1')
              			->where('p.status_id < 5');
		$stmt = $select->query();			
      	return $stmt->fetchAll();
    }
	public function getPapersFilter($sort, $dir, $filter){
    	$table = $this->getTable();
      	$db = $table->getAdapter();			
		$select = $db->select()->from(array('p' => 'papers'), 
		array('id', 'title', 'date_sent', 'abstract', 'file_pdf', 'file_doc', 'isActive',
			'isAssigned', 'email', 'authors', 'area_id', 'category_id', 'status_id'))
							 ->join(array('ps' => 'paper_status'), 'p.status_id = ps.id', array('status_name' => 'name_en'))
                             ->join(array('a' => 'areas'), 'a.id = p.area_id', array('area_short' => 'short', 'area_name_en' => 'name_en'))
                             ->join(array('c' => 'categories'), 'c.id = p.category_id', array('category_name_en' => 'name_en'))
              			->where('p.isActive = 1');
        if(isset($filter)){
            foreach($filter as $f){
                $q = $f['field'] . ' LIKE ?'; 
                $v = '%' . $f['data']['value'] . '%';                
                $select->where($q, $v);
            }
        }
		$select->order($sort . ' ' . $dir);
		$stmt = $select->query();			
      	return $stmt->fetchAll();
    }
    public function getAll($sort, $dir){
    	$table = $this->getTable();
      	$db = $table->getAdapter();			
		$select = $db->select()->from(array('p' => 'papers'), 
		array('id', 'title', 'date_sent', 'abstract', 'file_pdf', 'file_doc', 'isActive',
			'isAssigned', 'email', 'authors', 'area_id', 'category_id', 'status_id'))
							 ->join(array('ps' => 'paper_status'), 'p.status_id = ps.id', array('status_name' => 'name_mk'))
                             ->join(array('a' => 'areas'), 'a.id = p.area_id', array('area_short' => 'short', 'area_name' => 'name_mk'))
                             ->join(array('c' => 'categories'), 'c.id = p.category_id', array('category_name' => 'name_mk'))
              			->where('p.isActive = 1');        
		$select->order($sort . ' ' . $dir);
		$stmt = $select->query();			
      	return $stmt->fetchAll();
    }
    public function getTotalFilter($filter){
    	$table = $this->getTable();		 					        
        $select = $table->select();
        $select->from($table,array('count' => 'COUNT(id)'));
        if(isset($filter)){
            foreach($filter as $f){
                $q = $f['field'] . ' LIKE ?'; 
                $v = '%' . $f['data']['value'] . '%';                
                $select->where($q, $v);
            }
        }      
		$res = $table->fetchRow($select);        
        $c = $res->toArray();
    	return $c['count'];
    }
   public function getReviewedPapers($sort, $dir, $filter){
    	$table = $this->getTable();
      	$db = $table->getAdapter();			
		$select = $db->select()->from(array('p' => 'papers'), 
		array('id', 'title', 'date_sent', 'abstract', 'file_pdf', 'file_doc', 'isActive',
			'isAssigned', 'email', 'authors', 'area_id', 'category_id', 'status_id'))
							 ->join(array('ps' => 'paper_status'), 'p.status_id = ps.id', array('status_name' => 'name_en'))
                             ->join(array('a' => 'areas'), 'a.id = p.area_id', array('area_short' => 'short', 'area_name_en' => 'name_en'))
                             ->join(array('c' => 'categories'), 'c.id = p.category_id', array('category_name_en' => 'name_en'))
                             ->join(array('as' => 'assignments'), 'as.paper_id = p.id', array())
                             ->join(array('r' => 'reviews'), 'r.assignment_id = as.id', array())
              			->where('p.isActive = 1');
        if(isset($filter)){
            foreach($filter as $f){
                $q = $f['field'] . ' LIKE ?'; 
                $v = '%' . $f['data']['value'] . '%';                
                $select->where($q, $v);
            }
        }
		$select->order($sort . ' ' . $dir);
		$stmt = $select->query();			
      	return $stmt->fetchAll();
    }
	public function getById($paper_id){
		$table = $this->getTable();		 			
		$db = $table->getAdapter();			
		$select = $db->select()->from(array('p' => 'papers'))
							 ->join(array('c' => 'categories'), 'p.category_id = c.id',
              			array('category_name_en' => 'name_en', 'category_name_mk' => 'name_mk'))
							->join(array('a' => 'areas'), 'p.area_id = a.id',
							array('area_name_en' => 'name_en', 'area_name_mk' => 'name_mk'))
						->where('p.id = ?', $paper_id);
		$stmt = $select->query();			
      	return $stmt->fetchAll();
	}
	public function canAssign($paper_id, $participant_id){
		$table = $this->getTable();		 					        
        $select = $table->select();
        $select->from($table, array('count' => 'COUNT(id)'))
				->where('id = ?', $paper_id)
				->where('participant_id = ?', $participant_id);
		$res = $table->fetchRow($select);        
        $c = $res->toArray();
    	return $c['count'];
	}
    public function fetchEntry($id)
    {
        $table = $this->getTable();
        $select = $table->select()->where('id = ?', $id);    
        return $table->fetchRow($select);
    } 	
   
}
